﻿using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using LBKDHelper.Models;
using LBKDHelper.PluginHelpers;
using LBKDHelper.PluginTemplates;
using System;
using System.Collections.Generic;
using System.ComponentModel;

namespace LM.YF.K3Cloud.Plugin
{
    [Description("运费分析报表插件"), HotUpdate]
    public class FreightAnalysisReportPlugin : ReportPluginTemplate
    {
        #region MyRegion

        readonly ElementKeySchema F_QLTK_Carrier = new ElementKeySchema
        {
            FormKey = "F_QLTK_Carrier",
            OrmKey = "F_QLTK_Carrier"
        };

        readonly ElementKeySchema F_QLTK_NoticeOrder = new ElementKeySchema
        {
            FormKey = "F_QLTK_NoticeOrder",
            OrmKey = "F_QLTK_NoticeOrder"
        };

        readonly ElementKeySchema F_QLTK_PlanCode = new ElementKeySchema
        {
            FormKey = "F_QLTK_PlanCode",
            OrmKey = "F_QLTK_PlanCode"
        };

        /// <summary>
        /// 通知单日期
        /// </summary>
        readonly ElementKeySchema F_QLTK_StartDate1 = new ElementKeySchema
        {
            FormKey = "F_QLTK_StartDate1",
            OrmKey = "F_QLTK_StartDate1"
        };

        readonly ElementKeySchema F_QLTK_EndDate1 = new ElementKeySchema
        {
            FormKey = "F_QLTK_EndDate1",
            OrmKey = "F_QLTK_EndDate1"
        };

        /// <summary>
        /// 组单日期
        /// </summary>
        readonly ElementKeySchema F_QLTK_StartDate2 = new ElementKeySchema
        {
            FormKey = "F_QLTK_StartDate2",
            OrmKey = "F_QLTK_StartDate2"
        };

        readonly ElementKeySchema F_QLTK_EndDate2 = new ElementKeySchema
        {
            FormKey = "F_QLTK_EndDate2",
            OrmKey = "F_QLTK_EndDate2"
        };

        /// <summary>
        /// 过滤框-业务起始日期
        /// </summary>
        readonly ElementKeySchema F_QLTK_BizDate1 = new ElementKeySchema
        {
            FormKey = "F_QLTK_BizDate1",
            OrmKey = "F_QLTK_BizDate1"
        };

        /// <summary>
        /// 报表-业务起始日期
        /// </summary>
        readonly ElementKeySchema F_QLTK_Text_re5 = new ElementKeySchema
        {
            FormKey = "F_QLTK_Text_re5",
            OrmKey = "F_QLTK_Text_re5"
        };

        readonly ElementKeySchema F_QLTK_BizDate2 = new ElementKeySchema
        {
            FormKey = "F_QLTK_BizDate2",
            OrmKey = "F_QLTK_BizDate2"
        };

        readonly ElementKeySchema F_QLTK_Text_re5_apv = new ElementKeySchema
        {
            FormKey = "F_QLTK_Text_re5_apv",
            OrmKey = "F_QLTK_Text_re5_apv"
        };

        #endregion

        public FreightAnalysisReportPlugin()
            : base(reportType: ReportType.REPORTTYPE_NORMAL)
        { }

        public override void Initialize()
        {
            base.Initialize();
            // 不在 UI 设计列
            ReportProperty.IsUIDesignerColumns = false;
            // 账表名称
            ReportProperty.ReportName = new LocaleValue("运费分析报表");
            //// 设置精度
            //var decimalControlFieldList = new List<DecimalControlField>
            //{
            //    new DecimalControlField("decimal_length", "交货数量"),// 生效
            //    new DecimalControlField("decimal_length", "j.总重量")// 不生效
            //};
            //ReportProperty.DecimalControlFieldList = decimalControlFieldList;
        }

        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            var header = new ReportHeader();
            header.AddChild("通知单号", new LocaleValue("通知单号", Context.UserLocale.LCID));
            header.AddChild("客户", new LocaleValue("客户", Context.UserLocale.LCID));
            header.AddChild("运输区域编码", new LocaleValue("运输区域编码", Context.UserLocale.LCID));
            header.AddChild("运输区域", new LocaleValue("运输区域", Context.UserLocale.LCID));
            header.AddChild("交货地点编码", new LocaleValue("交货地点编码", Context.UserLocale.LCID));
            header.AddChild("交货地点", new LocaleValue("交货地点", Context.UserLocale.LCID));
            header.AddChild("交货地址", new LocaleValue("交货地址", Context.UserLocale.LCID));
            header.AddChild("物料编码", new LocaleValue("物料编码", Context.UserLocale.LCID));
            header.AddChild("物料", new LocaleValue("物料", Context.UserLocale.LCID));
            header.AddChild("规格型号", new LocaleValue("规格型号", Context.UserLocale.LCID));
            header.AddChild("出货仓库", new LocaleValue("出货仓库", Context.UserLocale.LCID));
            header.AddChild("交货数量", new LocaleValue("交货数量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("总重量", new LocaleValue("总重量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("重量单位", new LocaleValue("重量单位", Context.UserLocale.LCID));
            header.AddChild("出库数量", new LocaleValue("出库数量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("交货单位", new LocaleValue("交货单位", Context.UserLocale.LCID));
            header.AddChild("成本中心", new LocaleValue("成本中心", Context.UserLocale.LCID));
            header.AddChild("货运单号", new LocaleValue("货运单号", Context.UserLocale.LCID));
            header.AddChild("发货组织", new LocaleValue("发货组织", Context.UserLocale.LCID));
            header.AddChild("成本结算日期", new LocaleValue("成本结算日期", Context.UserLocale.LCID), SqlStorageType.SqlSmalldatetime);
            header.AddChild("应付单编号", new LocaleValue("应付单编号", Context.UserLocale.LCID));
            header.AddChild("承运商编码", new LocaleValue("承运商编码", Context.UserLocale.LCID));
            header.AddChild("承运商", new LocaleValue("承运商", Context.UserLocale.LCID));
            header.AddChild("运输路线", new LocaleValue("运输路线", Context.UserLocale.LCID));
            header.AddChild("含税单价", new LocaleValue("含税单价", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("费用类型", new LocaleValue("费用类型", Context.UserLocale.LCID));
            header.AddChild("交货单运费", new LocaleValue("交货单运费", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("运费", new LocaleValue("运费", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("运输计划点编号", new LocaleValue("运输计划点编号", Context.UserLocale.LCID));
            header.AddChild("运输计划点", new LocaleValue("运输计划点", Context.UserLocale.LCID));
            header.AddChild("组单时间", new LocaleValue("组单日期", Context.UserLocale.LCID), SqlStorageType.SqlSmalldatetime);

            return header;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);

            // 指定默认的排序字段
            var seqField = GetOrderColumnSQL(filter, "b.FBILLNO desc");
            // 获取自定义过滤框内的过滤条件
            var customerFilters = filter.FilterParameter.CustomFilter;
            var carrierId = (customerFilters[F_QLTK_Carrier.OrmKey] as DynamicObject)?["Id"].ToString() ?? "";
            var noticeOrderNo = customerFilters[F_QLTK_NoticeOrder.OrmKey]?.ToString() ?? "";
            var planCodeId = (customerFilters[F_QLTK_PlanCode.OrmKey] as DynamicObject)?["Id"].ToString() ?? "";

            var startDate1 = (customerFilters[F_QLTK_StartDate1.OrmKey] as DateTime?)?.ToString("yyyy-MM-dd") ?? "";
            var endDate1 = customerFilters[F_QLTK_EndDate1.OrmKey] as DateTime?;
            var endDate1String = "";
            if (endDate1 != null)
            {
                endDate1String = endDate1.Value.AddDays(1).ToString("yyyy-MM-dd");
            }

            var startDate2 = (customerFilters[F_QLTK_StartDate2.OrmKey] as DateTime?)?.ToString("yyyy-MM-dd") ?? "";
            var endDate2 = customerFilters[F_QLTK_EndDate2.OrmKey] as DateTime?;
            var endDate2String = "";
            if (endDate2 != null)
            {
                endDate2String = endDate2.Value.AddDays(1).ToString("yyyy-MM-dd");
            }

            var bizDate1 = (customerFilters[F_QLTK_BizDate1.OrmKey] as DateTime?)?.ToString("yyyy-MM-dd") ?? "";
            var bizDate2 = customerFilters[F_QLTK_BizDate2.OrmKey] as DateTime?;
            var bizDate2String = "";
            if (bizDate2 != null)
            {
                bizDate2String = bizDate2.Value.AddDays(1).ToString("yyyy-MM-dd");
            }

            var sqlParams = new List<SqlParam>
            {
                new SqlParam("@carrier_id", KDDbType.String, carrierId),
                new SqlParam("@notice_order_no", KDDbType.String, $"%{noticeOrderNo}%"),
                new SqlParam("@plan_code_id", KDDbType.String, planCodeId),
                new SqlParam("@start_date1", KDDbType.String, startDate1),
                new SqlParam("@end_date1", KDDbType.String, endDate1String),
                new SqlParam("@start_date2", KDDbType.String, startDate2),
                new SqlParam("@end_date2", KDDbType.String, endDate2String),
                new SqlParam("@biz_date1", KDDbType.String, bizDate1),
                new SqlParam("@biz_date2", KDDbType.String, bizDate2String)
            };

            var sql = $@"/*dialect*/
            with 运输区域 as (
	            select a.FID, a.FNUMBER, b.FNAME
	            from RDLV_t_Transportation as a
	            inner join RDLV_t_Transportation_L as b on b.FLocaleID = 2052 and b.FID = a.FID
            )
            , 物料 as (
	            select a.FMATERIALID, a.FNUMBER, b.FNAME, b.FSPECIFICATION
	            from T_BD_MATERIAL as a
	            inner join T_BD_MATERIAL_L as b on b.FLOCALEID = 2052 and b.FMATERIALID = a.FMATERIALID
            )
            , 运费应付单 as (
	            select a.FID, a.FENTRYID, b.FBILLNO, c.FSID as '货运单分录ID'
		            , '费用类型' = case b.F_FEE_TYPE
			            when 1 then '标准费用'
			            when 2 then '运费结算'
			            when 3 then '进仓费结算'
			            when 4 then '其它费用' end
		            , a.FPRICE as '含税单价', b.FDATE as '结算日期'
	            from T_AP_PAYABLEENTRY as a-- 应付单分录
	            inner join T_AP_PAYABLE as b on b.FID = a.FID-- 应付单
	            inner join T_AP_PAYABLE_LK as c on c.FRULEID in (
		            -- 货运单下推
		            '5a27b083-1a40-405b-85ab-6e0206952a14'
		            , '5db4c921-1912-4c7a-bf36-b2513f5f5bee'
		            , '97952d76-d612-9c12-11ee-b4fd4363ba0b'
	            ) and c.FENTRYID = a.FENTRYID
            )
            , 运输计划点 as (
	            select a.FID, a.FNUMBER, b.FNAME
	            from RDLV_t_PlanCode as a
	            inner join RDLV_t_PlanCode_L as b on b.FLOCALEID = 2052 and b.FID = a.FID
            )
            , 货运单 as (
	            select a.FID, e.FBILLNO, a.FEntryID, a.F_RDLV_SOURCEENTRYID as '通知单分录ID'
		            , b.FNAME as '重量单位', c.FNAME as '交货单位', d.FNAME as '成本中心'
		            , f.FBILLNO as '应付单编号', g.FNAME as '运输路线', a.F_QLTK_PICES as '出库数量'
		            , a.F_RDLV_ROWWEIGH as '总重量', f.结算日期 as '成本结算日期', f.含税单价
		            , f.费用类型, h.FID as '运输计划点内码', h.FNUMBER as '运输计划点编号', h.FNAME as '运输计划点'
		            , e.F_RDLV_CREATEDATE as '组单时间', e.F_RDLV_CARRIER as '承运商内码'
		            , a.F_RDLV_ROWAMT as '明细运费', a.F_RDLV_ACTAMT as '实际运费'
		            , i.FNAME as '发货组织'
	            from RDLV_t_DiliveOrdEntry as a
	            left join T_BD_UNIT_L as b on b.FLOCALEID = 2052 and b.FUNITID = a.F_RDLV_WEIGHUNIT
	            left join T_BD_UNIT_L as c on c.FLOCALEID = 2052 and c.FUNITID = a.F_RDLV_OUTUNIT
	            left join T_BD_DEPARTMENT_L as d on d.FLOCALEID = 2052 and d.FDEPTID = a.F_RDLV_COSTCENTER
	            inner join RDLV_t_DiliveOrder as e on e.FID = a.FID
	            left join 运费应付单 as f on f.货运单分录ID = a.FEntryID
	            left join RDLV_t_TransfRute_L as g on g.FLocaleID = 2052 and g.FID = e.F_RDLV_TRANSFERRUTE
	            left join 运输计划点 as h on h.FID = e.F_RDLV_STARTCODE
	            inner join T_ORG_ORGANIZATIONS_L as i on i.FLOCALEID = 2052 and i.FORGID = a.F_RDLV_STOCKORG2
            )
            , 供应商 as (
	            select a.FSUPPLIERID, a.FNUMBER, b.FNAME
	            from T_BD_SUPPLIER as a
	            inner join T_BD_SUPPLIER_L as b on b.FLOCALEID = 2052 and b.FSUPPLIERID = a.FSUPPLIERID
            )

            select b.FBILLNO as '通知单号', a.FSHIPMENTSTOCKID as '出货仓库ID', c.FNAME as '出货仓库'
	            , b.FCUSTOMERID as '客户ID', d.FNAME as '客户', b.F_RDLV_TRSANFAREA as '运输区域ID'
	            , e.FNUMBER as '运输区域编码', e.FNAME as '运输区域', a.FDELIVERYLOC as '交货地点ID'
	            , g.FNUMBER as '交货地点编码', g.FNAME as '交货地点', g.FADDRESS as '交货地址'
	            , a.FMATERIALID as '物料ID', h.FNUMBER as '物料编码', h.FNAME as '物料'
	            , h.FSPECIFICATION as '规格型号', a.FQTY as '交货数量', j.总重量, j.重量单位
	            , j.出库数量, j.交货单位, j.成本中心, j.FBILLNO as '货运单号', j.成本结算日期
	            , j.应付单编号, r.FNUMBER as '承运商编码', r.FNAME as '承运商', j.运输路线, j.含税单价
	            , j.费用类型, j.运输计划点编号, j.运输计划点, j.组单时间, j.发货组织
	            , isnull(j.明细运费, a.F_ESTIMATED_FREIGHT) as '交货单运费'
	            , isnull(j.实际运费, a.F_ACTUAL_FREIGHT) as '运费'
	            , 3 as 'decimal_length'
            	, {seqField}
            into {tableName}
            from T_SAL_DELIVERYNOTICEENTRY as a-- 通知单分录
            inner join T_SAL_DELIVERYNOTICE as b on b.FID = a.FID-- 通知单
            left join T_BD_STOCK_L as c on c.FLOCALEID = 2052 and c.FSTOCKID = a.FSHIPMENTSTOCKID
            inner join T_BD_CUSTOMER_L as d on d.FLOCALEID = 2052 and d.FCUSTID = b.FCUSTOMERID
            left join 运输区域 as e on e.FID = b.F_RDLV_TRSANFAREA
            left join T_BD_CUSTLOCATION as g on g.FENTRYID = a.FDELIVERYLOC
            inner join 物料 as h on h.FMATERIALID = a.FMATERIALID
            left join 货运单 as j on j.通知单分录ID = a.FENTRYID-- 货运单分录
            left join 供应商 as r on r.FSUPPLIERID = j.承运商内码
            where 1 = 1
            and (@start_date1 = '' or b.FDATE >= @start_date1)
            and (@end_date1 = '' or b.FDATE < @end_date1)
            and (@carrier_id = '' or j.承运商内码 = @carrier_id)
            and (@notice_order_no = '%%' or b.FBILLNO like @notice_order_no)
            and (@plan_code_id = '' or j.运输计划点内码 = @plan_code_id)
            and (@start_date2 = '' or j.组单时间 >= @start_date2)
            and (@end_date2 = '' or j.组单时间 < @end_date2)
            --and (@biz_date1 = '' or j.成本结算日期 >= @biz_date1)
            --and (@biz_date2 = '' or j.成本结算日期 < @biz_date2)
            and 1 = 1;";

            DBUtils.Execute(Context, sql, sqlParams);
        }

        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            // 将自定义过滤框内的过滤条件赋值到账表单据头的过滤条件中，以便将具体的过滤条件展示给用户
            var reportTitles = new ReportTitles();
            var customerFilter = filter.FilterParameter.CustomFilter;
            if (customerFilter == null)
            {
                return reportTitles;
            }

            var carrier = customerFilter[F_QLTK_Carrier.OrmKey] as DynamicObject;
            if (carrier != null)
            {
                var carrierName = PluginUtils.GetLocaleValue(carrier);
                reportTitles.AddTitle(F_QLTK_Carrier.FormKey, carrierName);
            }

            var noticeOrderNo = customerFilter[F_QLTK_NoticeOrder.OrmKey]?.ToString();
            if (noticeOrderNo != null)
            {
                reportTitles.AddTitle(F_QLTK_NoticeOrder.FormKey, noticeOrderNo);
            }

            var planCode = customerFilter[F_QLTK_PlanCode.OrmKey] as DynamicObject;
            if (planCode != null)
            {
                var planCodeName = PluginUtils.GetLocaleValue(planCode);
                reportTitles.AddTitle(F_QLTK_PlanCode.FormKey, planCodeName);
            }

            var startDate1 = customerFilter[F_QLTK_StartDate1.OrmKey];
            if (startDate1 != null)
            {
                var date = Convert.ToDateTime(startDate1).ToString("yyyy-MM-dd");
                reportTitles.AddTitle(F_QLTK_StartDate1.FormKey, date);
            }

            var endDate1 = customerFilter[F_QLTK_EndDate1.OrmKey]?.ToString();
            if (endDate1 != null)
            {
                var date = Convert.ToDateTime(endDate1).ToString("yyyy-MM-dd");
                reportTitles.AddTitle(F_QLTK_EndDate1.FormKey, date);
            }

            var startDate2 = customerFilter[F_QLTK_StartDate2.OrmKey]?.ToString();
            if (startDate2 != null)
            {
                var date = Convert.ToDateTime(startDate2).ToString("yyyy-MM-dd");
                reportTitles.AddTitle(F_QLTK_StartDate2.FormKey, date);
            }

            var endDate2 = customerFilter[F_QLTK_EndDate2.OrmKey]?.ToString();
            if (endDate2 != null)
            {
                var date = Convert.ToDateTime(endDate2).ToString("yyyy-MM-dd");
                reportTitles.AddTitle(F_QLTK_EndDate2.FormKey, date);
            }

            var bizDate1 = customerFilter[F_QLTK_BizDate1.OrmKey]?.ToString();
            if (bizDate1 != null)
            {
                var date = Convert.ToDateTime(bizDate1).ToString("yyyy-MM-dd");
                reportTitles.AddTitle(F_QLTK_Text_re5.FormKey, date);
            }

            var bizDate2 = customerFilter[F_QLTK_BizDate2.OrmKey]?.ToString();
            if (bizDate2 != null)
            {
                var date = Convert.ToDateTime(bizDate2).ToString("yyyy-MM-dd");
                reportTitles.AddTitle(F_QLTK_Text_re5_apv.FormKey, date);
            }

            return reportTitles;
        }
    }
}
